﻿package batchload;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class TestBatchLoad003 {
    
    Connection          conn            = null;
    PreparedStatement   statement       = null;  
    Statement           statement2      = null;
    static int          TEST_VALUE      ;          
    
    //기본생성자
    public TestBatchLoad003(){
        TEST_VALUE = 100;
    }
    
    //DB접속
    public void DBConn(){
        try {
            //Sybase ASE 
            Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
            conn = DriverManager.getConnection( "jdbc:sybase:Tds:58.76.19.47:5000/DMASIASE", "aisase","aisase");
            
//            //Sybase ASE JDTS
//            Class.forName("net.sourceforge.jtds.jdbc.Driver");
//            conn = DriverManager.getConnection( "jdbc:jtds:sybase://58.76.19.47:5000/DMASIASE;charset=euc-kr", "aisase","aisase");
            
            //Sybase IQ
//            Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
//            conn = DriverManager.getConnection( "jdbc:sybase:Tds:58.76.19.47:2361", "aisiq","aisiq");

            //ORACLE
//            Class.forName("oracle.jdbc.driver.OracleDriver");
//            conn = DriverManager.getConnection("jdbc:oracle:thin:@58.76.19.148:1522:ORCL","scott","tiger");
            
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block            
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //입력값
    public void ValueSet(){
        
        do{
            System.out.print("테스트 몇회수행?? [숫자를 넣으세요]: ");
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            int input_int;
            
            try {
                input_int = Integer.parseInt(br.readLine().trim());
            } catch (Exception e) {
                continue;
            }
            TEST_VALUE = input_int;
            break;
        }while(true) ;
    }
    
    //batch수행
    public void excute(){
        
        try {
            
            statement = conn.prepareStatement(makeSql("TABLEX",2));
            conn.setAutoCommit(false);
            long stime = System.currentTimeMillis(); //시작시간
            for(int i = 0 ; i < TEST_VALUE ; i++){
//                statement.clearParameters();
                statement.setInt(1, i);
                statement.setString(2, "JunSeok"+i);
                statement.addBatch();
            }
            statement.executeBatch();
            conn.setAutoCommit(true);
            
            long etime  = System.currentTimeMillis();
            System.out.println("Total ruunig time : " + (etime - stime) + "ms"); 
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        } 
    }
    
   
    //건by건
    public void excute2(){
        
        try {
            statement = conn.prepareStatement(makeSql("TEST_TABLE_100",100));
            
            long stime = System.currentTimeMillis(); //시작시간
            
            for(int i = 0 ; i < TEST_VALUE ; i++){
                statement.clearParameters();
                statement.setInt(1, i);
                statement.setString(2, "ABCDEFGHIJ");
                statement.executeUpdate();
            }
            
            long etime  = System.currentTimeMillis();
            System.out.println("Total ruunig time : " + (etime - stime) + "ms");
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("error");
        }
    }
    
    //sql 작성
    public String makeSql(String tableName, int count ){
        StringBuilder SQL = new StringBuilder();
        
        SQL.append("INSERT INTO ");
        SQL.append(tableName);
        SQL.append(" VALUES (");
        for(int i = 0 ; i < count ; i++){
            if(i == 0){
                SQL.append(" ? ");
            }else{
                SQL.append(", ? ");
            }
        }
        SQL.append(" )");
        
//        System.out.println(SQL.toString());
        return SQL.toString();
    }//sql 작성 End
    
    //table 초기화
    public void clearTable(String tableName){
        String clear_sql = null;
        
        try {
            statement2 = conn.createStatement() ;
            clear_sql ="TRUNCATE TABLE " + tableName;
            statement2.execute(clear_sql) ;            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("SQL error");
        } catch (Exception e){
            e.printStackTrace();
            System.out.println("error");
        }
    }//table 초기화 End
    
    public static void main(String[] args){
        
//        TestBatchLoad003 ob = new TestBatchLoad003();
//        ob.DBConn();
        
//        ob.ValueSet();
        
//        System.out.println("prepareStatement batch수행");
//        ob.excute();
//        
//        System.out.println("prepareStatement 건by건");
//        ob.excute2();
//        
//        ob.clearTable("TABLEX");
//        ob.clearTable("TABLEX2");
//        System.out.println("테이블초기화 완료");
        
        
        System.out.println("============================");
        TestBatchLoad001 ob1 = new TestBatchLoad001();
        ob1.DBConn();
        
        System.out.println("Statement batch수행");
        ob1.excute();
        ob1.clearTable("TEST_TABLE_100");
        
        System.out.println("Statement 건by건");
        ob1.excute2();
        ob1.clearTable("TEST_TABLE_100");
        
        System.out.println("테이블초기화 완료");
    }

}
